package com.zhongyi.db.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.log4j.Logger;

import com.zhongyi.db.DBManager;

/**
 * 数据库操作类
 * <p>
 * 
 *
 * @author <a href="mailto:lysongfei@gmail.com">songfei</a>
 * @version 1.0, 2012-3-8
 */
public class DBManagerImpl implements DBManager {
	
	/* 注入dbcp数据源  */
	public BasicDataSource dataSource;
	
	public void setDataSource(BasicDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public int deleteFromDB(String sql, Object[] params) {
		int res = -1;
		Connection conn = null;
		QueryRunner qRunner = null;
		try {
			qRunner = new QueryRunner();
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);
			res = qRunner.update(conn,sql, params);
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logger.error("error rollback delete operation ... ", e1);
			}
			logger.error("error when delete data from database ... ",e);
		}finally{
			DbUtils.closeQuietly(conn);
		}
		return res;
	}
	
	public int insertToDB(String sql, Object[] params) {
		int res = -1;
		Connection conn = null;
		QueryRunner qRunner = null;
		try {
			qRunner = new QueryRunner();
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);
			res = qRunner.update(conn,sql, params);
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logger.error("error rollback insert operation ... ", e1);
			}
			logger.error("error when insert data to database ... ",e);
		}finally{
			DbUtils.closeQuietly(conn);
		}
		return res;
	}


	public List queryForMapList(String sql, Object[] params) {
		List mapList = null;
		Connection conn = null;
		QueryRunner qRunner = null;
		try {
			qRunner = new QueryRunner();
			conn = dataSource.getConnection();
			mapList = qRunner.query(conn,sql, new MapListHandler(),params);
		} catch (SQLException e) {
			logger.error("error when query data from database ... ",e);
		}finally{
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
//			DbUtils.closeQuietly(conn);
		}
		return mapList;
	}

	public <T>List<T> queryForBeanList(String sql, Object[] params,Class<T> entityClass) {
		List<T> beanList = null;
		Connection conn = null;
		QueryRunner qRunner = null;
		try {
			qRunner = new QueryRunner();
			conn = dataSource.getConnection();
			beanList = (List<T>) qRunner.query(conn,sql, new BeanListHandler(entityClass), params);
		} catch (SQLException e) {
			logger.error("error when query data from database ... ",e);
		}finally{
			DbUtils.closeQuietly(conn);
		}
		return beanList;
	}

	@Override
	public int updateDB(String sql, Object[] params) {
		int res = -1;
		Connection conn = null;
		QueryRunner qRunner = null;
		try {
			qRunner = new QueryRunner();
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);
			res = qRunner.update(conn,sql, params);
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				logger.error("error rollback update operation ... ", e1);
			}
			logger.error("error when update data to database ... ",e);
		}finally{
			DbUtils.closeQuietly(conn);
		}
		return res;
	} 
	
	@Override
	public Map queryForMap(String sql, Object[] params) {
		Map map = null;
		Connection conn = null;
		QueryRunner qRunner = null;
		try {
			qRunner = new QueryRunner();
			conn = dataSource.getConnection();
			map = qRunner.query(conn,sql, new MapHandler(),params);
		} catch (SQLException e) {
			logger.error("error when query data from database ... ",e);
		}finally{
			DbUtils.closeQuietly(conn);
		}
		return map;
	}
	
	public static Logger logger = Logger.getLogger (DBManagerImpl.class) ;

}
